#!/usr/bin/env node

/**
 * SQLite备用数据库初始化脚本
 * 当主数据库不可用时，使用SQLite作为备用数据库
 */

import sqlite3 from 'sqlite3';
import { fileURLToPath } from 'url';
import { dirname, join } from 'path';
import { existsSync, mkdirSync } from 'fs';

const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);

class SQLiteInitializer {
    constructor() {
        this.projectRoot = join(__dirname, '..', '..');
        this.dataDir = join(this.projectRoot, 'data');
        this.dbPath = join(this.dataDir, 'bike_wholesale.db');
        
        // 确保数据目录存在
        if (!existsSync(this.dataDir)) {
            mkdirSync(this.dataDir, { recursive: true });
        }
        
        this.db = null;
    }

    // 初始化数据库
    async initialize() {
        try {
            console.log('💾 初始化SQLite备用数据库...');
            
            // 创建数据库连接
            this.db = new sqlite3.Database(this.dbPath, (err) => {
                if (err) {
                    console.error('❌ SQLite数据库连接失败:', err.message);
                    throw err;
                }
                console.log('✅ SQLite数据库连接成功');
            });
            
            // 启用外键约束
            await this.runQuery('PRAGMA foreign_keys = ON');
            
            // 创建表结构
            await this.createTables();
            
            // 插入基础数据
            await this.insertBaseData();
            
            console.log('✅ SQLite备用数据库初始化完成');
            
        } catch (error) {
            console.error('❌ SQLite数据库初始化失败:', error.message);
            throw error;
        }
    }

    // 执行SQL查询
    runQuery(sql, params = []) {
        return new Promise((resolve, reject) => {
            this.db.run(sql, params, function(err) {
                if (err) {
                    reject(err);
                } else {
                    resolve(this);
                }
            });
        });
    }

    // 执行SQL查询并返回结果
    getQuery(sql, params = []) {
        return new Promise((resolve, reject) => {
            this.db.all(sql, params, (err, rows) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(rows);
                }
            });
        });
    }

    // 创建表结构
    async createTables() {
        console.log('📊 创建数据库表结构...');
        
        // 用户表
        await this.runQuery(`
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username VARCHAR(50) UNIQUE NOT NULL,
                email VARCHAR(100) UNIQUE NOT NULL,
                password_hash VARCHAR(255) NOT NULL,
                role VARCHAR(20) DEFAULT 'user',
                status VARCHAR(20) DEFAULT 'active',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        `);

        // 产品表
        await this.runQuery(`
            CREATE TABLE IF NOT EXISTS products (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name VARCHAR(200) NOT NULL,
                description TEXT,
                price DECIMAL(10,2) NOT NULL,
                category VARCHAR(50),
                image_url VARCHAR(500),
                stock_quantity INTEGER DEFAULT 0,
                status VARCHAR(20) DEFAULT 'active',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        `);

        // 博客文章表
        await this.runQuery(`
            CREATE TABLE IF NOT EXISTS blog_posts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title VARCHAR(200) NOT NULL,
                content TEXT,
                excerpt TEXT,
                author_id INTEGER,
                category VARCHAR(50),
                status VARCHAR(20) DEFAULT 'draft',
                published_at DATETIME,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (author_id) REFERENCES users (id)
            )
        `);

        // 订单表
        await this.runQuery(`
            CREATE TABLE IF NOT EXISTS orders (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                order_number VARCHAR(50) UNIQUE NOT NULL,
                user_id INTEGER,
                total_amount DECIMAL(10,2) NOT NULL,
                status VARCHAR(20) DEFAULT 'pending',
                shipping_address TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users (id)
            )
        `);

        // 系统配置表
        await this.runQuery(`
            CREATE TABLE IF NOT EXISTS system_config (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                config_key VARCHAR(100) UNIQUE NOT NULL,
                config_value TEXT,
                description TEXT,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        `);

        console.log('✅ 数据库表结构创建完成');
    }

    // 插入基础数据
    async insertBaseData() {
        console.log('📝 插入基础数据...');
        
        try {
            // 插入默认管理员用户
            const adminExists = await this.getQuery('SELECT id FROM users WHERE username = ?', ['admin']);
            if (adminExists.length === 0) {
                await this.runQuery(`
                    INSERT INTO users (username, email, password_hash, role, status)
                    VALUES (?, ?, ?, ?, ?)
                `, ['admin', 'admin@bike-wholesale.com', 'hashed_password_placeholder', 'admin', 'active']);
                console.log('✅ 默认管理员用户已创建');
            }

            // 插入示例产品
            const productsExist = await this.getQuery('SELECT id FROM products');
            if (productsExist.length === 0) {
                const sampleProducts = [
                    ['电动自行车 A1', '高性能城市电动自行车', 2999.00, 'electric', '/images/bike1.jpg', 50],
                    ['山地自行车 M2', '专业级山地自行车', 1899.00, 'mountain', '/images/bike2.jpg', 30],
                    ['公路自行车 R3', '轻量化公路自行车', 2599.00, 'road', '/images/bike3.jpg', 20],
                    ['折叠自行车 F4', '便携式折叠自行车', 1299.00, 'folding', '/images/bike4.jpg', 40]
                ];

                for (const product of sampleProducts) {
                    await this.runQuery(`
                        INSERT INTO products (name, description, price, category, image_url, stock_quantity)
                        VALUES (?, ?, ?, ?, ?, ?)
                    `, product);
                }
                console.log('✅ 示例产品数据已创建');
            }

            // 插入系统配置
            const configExists = await this.getQuery('SELECT id FROM system_config WHERE config_key = ?', ['site_name']);
            if (configExists.length === 0) {
                const systemConfigs = [
                    ['site_name', '自行车外贸站', '网站名称'],
                    ['site_description', '专业的自行车批发平台', '网站描述'],
                    ['contact_email', 'contact@bike-wholesale.com', '联系邮箱'],
                    ['default_language', 'zh-CN', '默认语言']
                ];

                for (const config of systemConfigs) {
                    await this.runQuery(`
                        INSERT INTO system_config (config_key, config_value, description)
                        VALUES (?, ?, ?)
                    `, config);
                }
                console.log('✅ 系统配置数据已创建');
            }

            console.log('✅ 基础数据插入完成');
            
        } catch (error) {
            console.warn('⚠️ 基础数据插入失败:', error.message);
            console.log('💡 数据库结构已创建，但基础数据插入失败');
        }
    }

    // 关闭数据库连接
    close() {
        if (this.db) {
            this.db.close((err) => {
                if (err) {
                    console.error('❌ 关闭数据库连接失败:', err.message);
                } else {
                    console.log('✅ 数据库连接已关闭');
                }
            });
        }
    }
}

// 执行初始化
const initializer = new SQLiteInitializer();

async function main() {
    try {
        await initializer.initialize();
        initializer.close();
        console.log('🎉 SQLite备用数据库初始化成功！');
        process.exit(0);
    } catch (error) {
        console.error('💥 SQLite备用数据库初始化失败:', error.message);
        initializer.close();
        process.exit(1);
    }
}

// 如果是直接运行此脚本
if (import.meta.url === `file://${process.argv[1]}`) {
    main();
}

export default SQLiteInitializer;